package com.saui.traceu.model;

import com.saui.traceu.model.Session;
import com.saui.traceu.model.TracePoint;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class TraceDBAdapter {
    public static final String KEY_TITLE = "title";
    public static final String KEY_ROWID = "_id";
    public static final String KEY_SESSION_TITLE = "title";
    public static final String KEY_SESSION_STARTTIME = "start_time";
    public static final String KEY_SESSION_ENDTIME = "end_time";
    public static final String KEY_SESSION_ISSAVED = "isSaved";
    public static final String KEY_DATA_SESSIONID = "session_id";
    public static final String KEY_DATA_LAT = "lat";
    public static final String KEY_DATA_LONG = "long";
    
    private DatabaseHelper mDbHelper;
    private SQLiteDatabase mDb;
    
    private static final String DATABASE_NAME = "trace";
    private static final String TABLE_SESSION = "session";
    private static final String TABLE_DATAPOINTS = "datapoints";
    private static final int DATABASE_VERSION = 2;
    private final Context mCtx;

    //database sql code for new table
    //used to hold the session information such as
    //title start and end time
    private static final String TABLECREATE_SESSION =
        "create table session (" +
        	"_id integer primary key autoincrement, " +
        	"title text not null, " +
            "start_time real not null, " +
            "end_time real, " +
            "isSaved boolen);";
    //database sql code for new table of datapoints
    //used to save the gps coordinates captured during a session
    private static final String TABLECREATE_DATAPOINTS =
        "create table datapoints (" +
        	"_id integer primary key autoincrement, " +
        	"session_id integer not null, " +
        	"lat real not null, " +
        	"long real not null);";
    
    //logic code
    
    private static class DatabaseHelper extends SQLiteOpenHelper {

        DatabaseHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
        	//temp variable used to insert dummy data
        	long sessionID;
        	//create tables in the database
        	db.execSQL(TABLECREATE_SESSION);
            db.execSQL(TABLECREATE_DATAPOINTS);
            
            //start saving dummy data for testing
            ContentValues initialValues = new ContentValues(); 
            initialValues.put("title", "TestSession1");
            initialValues.put("start_time", System.currentTimeMillis());
            initialValues.put("isSaved", true);
            //write the dummy test sesssion
            sessionID = db.insert(TABLE_SESSION, null, initialValues);
            
            initialValues.clear();
            //add gps points
            initialValues.put(KEY_DATA_LAT, 40.426);
            initialValues.put(KEY_DATA_LONG, -80.096);
            initialValues.put("session_id", sessionID);
            //save gps points
            db.insert(TABLE_DATAPOINTS, null, initialValues);
            //add gps points
            initialValues.clear();
            initialValues.put(KEY_DATA_LAT, 22.428);
            initialValues.put(KEY_DATA_LONG, -22.098);
            initialValues.put("session_id", sessionID);
            //save gps points
            db.insert(TABLE_DATAPOINTS, null, initialValues);
            //add
            initialValues.clear();
            initialValues.put(KEY_DATA_LAT, 33.430);
            initialValues.put(KEY_DATA_LONG, -33.1);
            initialValues.put("session_id", sessionID);
            //save
            db.insert(TABLE_DATAPOINTS, null, initialValues);
            
            //dummy session2
            initialValues.clear();
            initialValues.put("title", "TestSession2");
            initialValues.put("start_time", System.currentTimeMillis());
            initialValues.put("isSaved", true);
            //save session
            sessionID = db.insert(TABLE_SESSION, null, initialValues);
            //add points
            initialValues.clear();
            initialValues.put(KEY_DATA_LAT, -60.426);
            initialValues.put(KEY_DATA_LONG, 20.096);
            initialValues.put("session_id", sessionID);
            //save
            db.insert(TABLE_DATAPOINTS, null, initialValues);
            //
            initialValues.clear();
            initialValues.put(KEY_DATA_LAT, 20.428);
            initialValues.put(KEY_DATA_LONG, -60.098);
            initialValues.put("session_id", sessionID);
            
            db.insert(TABLE_DATAPOINTS, null, initialValues);
            initialValues.clear();
            initialValues.put(KEY_DATA_LAT, 41.430);
            initialValues.put(KEY_DATA_LONG, -20.1);
            initialValues.put("session_id", sessionID);
            
            db.insert(TABLE_DATAPOINTS, null, initialValues);
            
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        	//required 
        }
    }
    
    /**
     * Constructor - takes the context to allow the database to be
     * opened/created
     * 
     * @param ctx the Context within which to work
     */
    public TraceDBAdapter (Context ctx) {
        this.mCtx = ctx;
    }
    
    /**
     * Open the session database. If it cannot be opened, try to create a new
     * instance of the database. If it cannot be created, throw an exception to
     * signal the failure
     * 
     * @return this (self reference, allowing this to be chained in an
     *         initialization call)
     * @throws SQLException if the database could be neither opened or created
     */
    public TraceDBAdapter open() throws SQLException {
        mDbHelper = new DatabaseHelper(mCtx);
        mDb = mDbHelper.getWritableDatabase();
        return this;
    }
    
    public void setDataPoints (Float[][] _values, Float sessionID){
    	
    	//create content values variable to store what we will put into DB
    	ContentValues newValues = new ContentValues();
    	
    	//itterate through the 2d array to put values into DB
    	for (int i = 0; i < _values.length; i++){
    		newValues.put(KEY_DATA_LAT, _values[i][1]);
    		newValues.put(KEY_DATA_LONG, _values[i][2]);
    		newValues.put("session_id", sessionID);
    		
    		//insert data into DB
    		mDb.insert(TABLE_DATAPOINTS, null, newValues);
    		//clear values for next itteration
    		newValues.clear();    		
    	}
    	
    	
    	mDb.insert(TABLE_DATAPOINTS, null, newValues);
    }
        
    public long createSession (Session s){
    	// info to create new record with
    	ContentValues newValues = new ContentValues();
    	//to store the resulting record ID = SessionID 
    	long sessionID;
    	
    	//set the data for the row 
    	newValues.put("title", s.getTitle());
    	newValues.put("start_time", s.getStartTime());
    	newValues.put("isSaved", false);
    	
    	// insert row and store resulting row number which will be the sessionID
    	sessionID = mDb.insert(TABLE_SESSION, null, newValues);
    	
    	return sessionID; // add mdb result
    }
    
    public Cursor getTracePointsBySessionID(Session s){
    	//cursor to put results into
    	Cursor mCursor =
    		//querey taking the session ID and getting the array 
    		mDb.query(true, TABLE_DATAPOINTS, new String[] {KEY_ROWID,
    				KEY_DATA_LAT, KEY_DATA_LONG}, "session_id" + "=" + String.valueOf(s.getId()), null,
                     null, null,KEY_ROWID,null);
    	
    	//check to see if cursor is valid and move to 
    	if (mCursor != null) {
    		mCursor.moveToFirst();
    		}
		return mCursor;
    	}
    public Cursor getTracePointsByLongID(long l){
    	//cursor to put results into
    	Cursor mCursor =
    		//querey taking the session ID and getting the array 
    		mDb.query(true, TABLE_DATAPOINTS, new String[] {KEY_ROWID,
    				KEY_DATA_LAT, KEY_DATA_LONG,KEY_DATA_SESSIONID}, "session_id" + "=" + l, null,
                     null, null,KEY_ROWID,null);
    	
    	//check to see if cursor is valid and move to 
    	if (mCursor != null) {
    		mCursor.moveToFirst();
    		}
		return mCursor;
    }
    public Cursor getAllSessions(){
    	//cursor to put results into
    	Cursor mCursor =
    		//querey taking the session ID and getting the array 
    		mDb.query(true, TABLE_SESSION, new String[] {KEY_ROWID, KEY_SESSION_TITLE, KEY_SESSION_STARTTIME,
                     KEY_SESSION_ENDTIME}, KEY_SESSION_ISSAVED +"="+ 1, null,
                     null, null,KEY_ROWID,null);
    	
    	//check to see if cursor is valid and move to 
    	if (mCursor != null) {
    		mCursor.moveToFirst();
    		}
		return mCursor;
    }
    
    public Session getLastSession(){
    	Cursor mCursor;
    	Session lastSession = new Session();
    	
    	mCursor =
    		//querey taking the session ID and getting the array 
    		mDb.query(true, TABLE_SESSION, new String[] {KEY_ROWID, KEY_SESSION_TITLE, KEY_SESSION_STARTTIME,
                     KEY_SESSION_ENDTIME,KEY_SESSION_ISSAVED}, null, null,
                     null, null,KEY_ROWID,null);
    	
    	//check to see if cursor is valid and move to 
    	if (mCursor != null) {
    		mCursor.moveToLast();
    	}
    	
    	lastSession.setTitle(mCursor.getString(mCursor.getColumnIndexOrThrow(KEY_SESSION_TITLE)));
    	lastSession.setId(mCursor.getLong(mCursor.getColumnIndexOrThrow(KEY_ROWID)));
    	lastSession.setStartTime(mCursor.getLong(mCursor.getColumnIndexOrThrow(KEY_SESSION_STARTTIME)));
    	lastSession.setEndTime(mCursor.getLong(mCursor.getColumnIndexOrThrow(KEY_SESSION_ENDTIME)));
    	
    	//(mCursor.getLong(mCursor.getColumnIndexOrThrow(KEY_SESSION_ISSAVED)) == 0 ? false : true);  	
    	lastSession.setSaved((mCursor.getLong(mCursor.getColumnIndexOrThrow(KEY_SESSION_ISSAVED)) == 0 ? false : true));
    	   	
    	return lastSession;
    }
    
    public void createTracePoints (TracePoint[] t){
    	
    	ContentValues newValues = new ContentValues();
    	
    	for (int i = 0; i < t.length; i++){
    		//retreive data points from array of tracepoints
    		newValues.put("lat", t[i].getLatitude());
    		newValues.put("long", t[i].getLongitude());
    		newValues.put("session_id", t[i].getSessionId());
    		
    		//insert data into DB
    		mDb.insert(TABLE_DATAPOINTS, null, newValues);
    		//clear values for next itteration
    		newValues.clear();    		
    	}
    	
    }
    
    public long updateSession (Session s){
    	// info to create new record with
    	ContentValues newValues = new ContentValues();
    	//to store the resulting record ID = SessionID 
    	long result;
    	
    	//set the data for the row 
    	newValues.put("title", s.getTitle());
    	newValues.put("start_time", s.getStartTime());
    	newValues.put("isSaved", s.isSaved());
    	newValues.put(KEY_SESSION_ENDTIME, s.getEndTime());
    	
    	// update row with new data
    	result = mDb.update(TABLE_SESSION, newValues, KEY_ROWID + "=" + String.valueOf(s.getId()), null);
    	
    	
    	return result; // add mdb result
    }

    /*
     * used to delete single or multiple sessions
     * ids is an array of session IDs to be delted
     * 
     */
    public long deleteSessions (long[] ids){
    	int result = 0;
    	//go through array of session IDs
    	for (int i = 0; i < ids.length; i++){
    		//delete session
    		result = mDb.delete(TABLE_SESSION, KEY_ROWID+"="+ String.valueOf(ids[i]), null);
    		//delete the gps points associated with this ession
    		mDb.delete(TABLE_DATAPOINTS, "session_id ="+ String.valueOf(ids[i]), null);
    	}
    	return result;
    }
    
    public long deleteUnsavedSessions (){
    	int result = 0;
    	
    	result = mDb.delete(TABLE_SESSION, KEY_SESSION_ISSAVED+"=0", null);
    	
    	return result;
    }
    
}
